﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;

namespace PDMCompare
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        #region 选择SQL脚本文件
        
        private void btnSQLFile_Click(object sender, EventArgs e)
        {
            if (dlgSQLFile.ShowDialog().Equals(DialogResult.OK))
                txtSQLFile.Text = dlgSQLFile.FileName;
        }

        #endregion

        #region 开始对比
        
        private void btnCompare_Click(object sender, EventArgs e)
        {
            txtResult.Text = "";

            // 获得数据库表结构
            Dictionary<string, TableInfo> dictFromDB = GetDictionaryFromDatabase();
            if (dictFromDB.Count == 0)
            {
                Log("无法获得数据库数据");
                return;
            }

            // 获得SQL脚本的表结构
            Dictionary<string, TableInfo> dictFromSQL = GetDictionaryFromSQLFile();
            if (dictFromSQL.Count == 0)
            {
                Log("无法从脚本文件获得数据");
                return;
            }

            // 使用脚本表结构对比数据库
            foreach (string tableName in dictFromSQL.Keys)
            {
                if (!dictFromDB.ContainsKey(tableName))
                {
                    Log(string.Format("***表{0}:数据库中缺少", tableName));
                    continue;
                }
                // 对比表信息
                CompareTableInfo(dictFromSQL[tableName], dictFromDB[tableName]);
            }

            Log("---对比完成");
        }

        #endregion

        #region 获得数据库的表结构

        private Dictionary<string, TableInfo> GetDictionaryFromDatabase()
        {
            // 建立与数据库连接
            SqlConnection conn = new SqlConnection( GetConnectString() );

            // 根据数据库类型选择执行合适的SQL
            SqlCommand cmd = conn.CreateCommand();
            if (chk2005.Checked)
                cmd.CommandText = GSql.T2005;
            else
                cmd.CommandText = GSql.T2000;

            Dictionary<string, TableInfo> dict = new Dictionary<string, TableInfo>();
            try
            {
                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    int i = 0;
                    // 获得表名
                    string tableName = reader.GetString(i++);

                    // 获得字段信息
                    FieldInfo field = new FieldInfo();
                    field.FieldName = reader.GetString(i++).Trim();
                    field.IsKey = Convert.ToInt32(reader.GetString(i++)) == 1 ? true : false;
                    field.FieldType = reader.GetString(i++).Trim();
                    field.Length = reader.GetInt16(i++);
                    field.FieldPrecision = reader.GetByte(i++);
                    field.Scale = reader.GetByte(i++);
                    field.CanNull = Convert.ToInt32(reader.GetString(i++)) == 1 ? true : false;
                    field.DefaultValue = reader.GetString(i++).Trim();

                    // 将结果放入字典
                    if (!dict.ContainsKey(tableName))
                    {
                        TableInfo table = new TableInfo();
                        table.TableName = tableName;
                        table.Fields.Add(field);

                        dict.Add(tableName, table);
                    }
                    else
                    {
                        dict[tableName].Fields.Add(field);
                    }
                }

                reader.Close();
            }
            
            catch (Exception ex)
            {
                Log(string.Format("数据库操作失败：{0}", ex.Message));
            }
            finally
            {
                conn.Close();
            }

            return dict;
        }

        #endregion

        #region 获得SQL文件的表结构

        private Dictionary<string, TableInfo> GetDictionaryFromSQLFile()
        {
            Dictionary<string, TableInfo> dict = new Dictionary<string, TableInfo>();
            // 读取SQL脚本
            string txtSql = GetSqlTxt();
            if (string.IsNullOrEmpty(txtSql))
                return dict;

            // 获得固定长度SQL类型的定义
            Dictionary<string, int> dictSqlType = GetSqlTypeLengthDictionary();

            // 获得域定义字典
            Dictionary<string, DomInfo> dictDom = GetDomDictionary(txtSql);

            // 获得表定义列表
            List<string> tables = GetTables(txtSql);

            // 分析每个表定义，建立TableInfo
            foreach (string txtTable in tables)
            {
                TableInfo tableInfo = new TableInfo();
                // 获取表名
                tableInfo.TableName = GetTableName(txtTable);

                // 获取主键字段表 
                Dictionary<string, bool> dictKey = GetTableKeyFields(txtTable);

                // 获取字段信息
                tableInfo.Fields.AddRange(GetFieldsInfo(txtTable, dictDom, dictSqlType, dictKey));

                // 加入字典
                dict.Add(tableInfo.TableName, tableInfo);
            }

            return dict;
        }

        #endregion

        #region 数据对比

        private void CompareTableInfo(TableInfo sqlInfo, TableInfo dbInfo)
        {
            bool pass = true;
            string log = "";

            Dictionary<string, bool> dictFind = new Dictionary<string, bool>();

            // 根据SQL脚本检查
            foreach ( FieldInfo sqlField in sqlInfo.Fields )
            {
                FieldInfo dbField = dbInfo.FindField(sqlField.FieldName);
                if (dbField == null)
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中字段在数据库中不存在\r\n", sqlField.FieldName);
                    pass = false;
                    continue;
                }

                // 主键对比
                if (!sqlField.IsKey.Equals(dbField.IsKey))
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中字段{1}主键\r\n", sqlField.FieldName , 
                        sqlField.IsKey ? "是" : "不是" );
                    pass = false;
                }

                // 字段类型
                if (!sqlField.FieldType.Equals(dbField.FieldType))
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中类型为{1}而数据库中为{2}\r\n", sqlField.FieldName,
                        sqlField.FieldType , dbField.FieldType );
                    pass = false;
                }

                if (sqlField.FieldPrecision == 0 )
                {
                    // 字段长度,当精度和小数位数不为0时不用对比长度
                    if (!sqlField.Length.Equals(dbField.Length))
                    {
                        log += string.Format("\t\t[{0}]:SQL脚本中长度为{1}而数据库中为{2}\r\n", sqlField.FieldName,
                            sqlField.Length, dbField.Length);
                        pass = false;
                    }
                }

                // 字段精度
                if (!sqlField.FieldPrecision.Equals(dbField.FieldPrecision))
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中数据精度为{1}而数据库中为{2}\r\n", sqlField.FieldName,
                        sqlField.FieldPrecision, dbField.FieldPrecision);
                    pass = false;
                }

                // 小数位数
                if (!sqlField.Scale.Equals(dbField.Scale))
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中小数位数为{1}而数据库中为{2}\r\n", sqlField.FieldName,
                        sqlField.Scale, dbField.Scale);
                    pass = false;
                }

                // 是否可为空
                if (!sqlField.CanNull.Equals(dbField.CanNull))
                {
                    log += string.Format("\t\t[{0}]:SQL脚本中字段{1}为空\r\n", sqlField.FieldName,
                        sqlField.CanNull ? "可" : "不可");
                    pass = false;
                }

            }

            // 根据数据库字段检查
            foreach (FieldInfo dbField in dbInfo.Fields)
            {
                if (!dictFind.ContainsKey(dbField.FieldName))
                {
                    log += string.Format("\t\t[{0}]:数据库中字段在SQL脚本中不存在\r\n", dbField.FieldName);
                    pass = false;
                }
            }

            if (!pass)
            {
                Log(string.Format("***表{0}", sqlInfo.TableName));
                Log(log);
            }
        }

        #endregion

        #region GetSqlTxt

        private string GetSqlTxt()
        {
            if (!File.Exists(txtSQLFile.Text))
                return string.Empty;

            return File.ReadAllText(txtSQLFile.Text);
        }

        #endregion

        #region GetSqlTypeLengthDictionary

        private Dictionary<string, int> GetSqlTypeLengthDictionary()
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();
            dict.Add("bit", 1);
            dict.Add("int", 4);
            dict.Add("smallint", 2);
            dict.Add("tinyint", 1);
            dict.Add("money", 8);
            dict.Add("smallmoney", 4);
            dict.Add("datetime", 8);
            dict.Add("smalldatetime", 4);
            dict.Add("uniqueidentifier", 16);
            dict.Add("timestamp", 8);
            dict.Add("float", 8);
            dict.Add("real", 4);
            dict.Add("text", 16);
            dict.Add("ntext", 16);
            dict.Add("image", 16);
            dict.Add("sql_variant", 8016);
            dict.Add("xml", -1);

            return dict;
        }

        #endregion

        #region GetDomDictionary

        private Dictionary<string, DomInfo> GetDomDictionary(string txtSql)
        {
            Regex regex = new Regex(GRegex.解析类型定义, RegexOptions.IgnoreCase);

            MatchCollection mc = regex.Matches(txtSql);

            Dictionary<string, DomInfo> dictDom = new Dictionary<string, DomInfo>();

            foreach (Match m in mc)
            {
                DomInfo domInfo = new DomInfo();
                domInfo.DomName = m.Groups["dom"].Value;
                domInfo.SqlType = m.Groups["sqltype"].Value;
                if (!string.IsNullOrEmpty(m.Groups["len"].Value))
                    domInfo.Length = Convert.ToInt32(m.Groups["len"].Value);
                if (!string.IsNullOrEmpty(m.Groups["int"].Value))
                    domInfo.Precision = Convert.ToInt32(m.Groups["int"].Value);
                if (!string.IsNullOrEmpty(m.Groups["scale"].Value))
                    domInfo.Scale = Convert.ToInt32(m.Groups["scale"].Value);

                dictDom.Add(domInfo.DomName, domInfo);
            }

            return dictDom;
        }
        #endregion

        #region GetTables

        private List<string> GetTables(string txtSql)
        {
            Regex regex = new Regex(GRegex.获取建表定义, RegexOptions.IgnoreCase);

            MatchCollection mc = regex.Matches(txtSql);

            List<string> tables = new List<string>();

            foreach (Match m in mc)
            {
                tables.Add(m.Groups[0].Value);
            }

            return tables;
        }

        #endregion

        #region GetTableName
        
        private string GetTableName ( string txtTable )
        {
            Regex regex = new Regex(GRegex.解析表名, RegexOptions.IgnoreCase);

            Match m = regex.Match(txtTable);

            return m.Groups["table"].Value;
        }

        #endregion

        #region GetFieldsInfo

        private List<FieldInfo> GetFieldsInfo(string txtTable, Dictionary<string, DomInfo> dictDom,
            Dictionary<string, int> dictSqlType, Dictionary<string, bool> dictKey)
        {
            Regex regex = new Regex(GRegex.解析字段定义, RegexOptions.IgnoreCase);

            MatchCollection mc = regex.Matches(txtTable);

            List<FieldInfo> fields = new List<FieldInfo>();

            foreach (Match m in mc)
            {
                FieldInfo field = new FieldInfo();

                field.FieldName = m.Groups["field"].Value;
                field.FieldType = m.Groups["type"].Value;
                // 获得DOM变量相关参数
                if (dictDom.ContainsKey(field.FieldName))
                {
                    field.Length = dictDom[field.FieldName].Length;
                    field.FieldPrecision = dictDom[field.FieldName].Precision;
                    field.Scale = dictDom[field.FieldName].Scale;

                    if ( dictSqlType.ContainsKey ( dictDom[field.FieldName].SqlType ) )
                        field.Length = dictSqlType[dictDom[field.FieldName].SqlType];

                    // 如果是2000的库，FieldType要将域定义进行转换
                    if (!chk2005.Checked)
                        field.FieldType = dictDom[field.FieldName].SqlType;
                }
                field.CanNull = m.Groups["cannull"].Value == "null" ? true : false;
                field.IsKey = dictKey.ContainsKey(field.FieldName);

                fields.Add(field);
            }

            return fields;
        }

        #endregion

        #region GetTableKeyFields

        private Dictionary<string, bool> GetTableKeyFields(string txtTable)
        {
            Regex regex = new Regex(GRegex.解析主键定义, RegexOptions.IgnoreCase);

            MatchCollection mc = regex.Matches(txtTable);

            Dictionary<string, bool> dict = new Dictionary<string, bool>();

            foreach (Match m in mc)
            {
                dict.Add(m.Groups["name"].Value, true);
            }

            return dict;
        }

        #endregion

        #region GetConnectString

        private string GetConnectString()
        {
            return string.Format("Database={0};Server={1};uid={2};pwd={3};max pool size=200;min pool size=1;timeout=150",
                txtDatabase.Text.Trim(), txtServer.Text.Trim(),
                txtUser.Text.Trim(), txtPassword.Text.Trim());
        }

        #endregion

        #region Log

        private void Log(string info)
        {
            txtResult.Text += info + "\r\n";
        }

        #endregion

        #region Form1_Load

        private void Form1_Load(object sender, EventArgs e)
        {
            txtDatabase.Text = "Budget";
            txtServer.Text = "192.168.1.115";
            txtUser.Text = "sa";
            txtPassword.Text = "sa";
            chk2005.Checked = true;
        }

        #endregion


    }
}